<?php
 
	/*
	 * Created by Nicky
	 *
	*/
	 /* permission name 
	 karPenilaian_Student
	 karPenilaian_Student_add
	 karPenilaian_Student_edit
	 */
	class Report_nilaiController extends CController{
		private $result = array("actionStatus"=>0,"actionMessage"=>"","model"=>array(),"actionError"=>array());	
		public $mainMenu = "Orang Tua";
		public $subMenu = "";
		public $title = "";		
		public $loginUser;		
		public function init() {
			parent::init();
			if (!LoginUser::isLogin("orangtua")){
				LoginUser::redirectToLoginPage("orangtua");
			}else{
				$this->loginUser = LoginUser::getUser();
			}
						
		}
		
	 
		
		//StudentController
		public function actionByStudent(){
			LoginUser::getPermission("orangtua_commbook");
			$this->subMenu = "Commbook";
			$this->layout ="//layouts/ly_blank";
			$student = $this->loginUser ->student;
	 		$kelas_tahun_ajaran_id = Yii::app()->request->getParam("kelas_tahun_ajaran_id","");	
			$student_id=$student->id;	
			$department_id=$student->department_id;				
						
 			$module= $this->loginUser ->module;
			
			
			$datenow=date("Y-m-d");
			$tahunajarans = MTahunAjaran::model()->findAll("is_active = TRUE   and department_id=:departmentId and (:datenow between start_date and end_date)  order by id",array(":departmentId"=>$department_id,":datenow"=>$datenow));

			if ($tahunajarans == null){
				$tahun_ajaran_id == "";
			}else{	
				$tahun_ajaran_id = Yii::app()->request->getParam("tahun_ajaran_id", $tahunajarans[0]->id);
			}
			
 		 
			$kelass = VStudentKelasTahunAjaran::model()->findAll("student_id=:student_id and tahun_ajaran_id=:tahun_ajaran_id  ",array(":student_id"=>$student_id,":tahun_ajaran_id"=>$tahun_ajaran_id));
 
			if ($kelass == null){
				$kelas_tahun_ajaran_id == "";
			}else{
				$kelas_tahun_ajaran_id = $kelas_tahun_ajaran_id == "" ? $kelass[0]["kelas_tahun_ajaran_id"] : $kelas_tahun_ajaran_id;
			}
 	 		
			$semester_id = Yii::app()->request->getParam("semester_id",""); 		
	 
	 
			$semesters = MSemester::model()->findAll("tahun_ajaran_id=:tahun_ajaran_id and is_active = TRUE and department_id='".$department_id."' order by id",array(":tahun_ajaran_id"=>$tahun_ajaran_id));
			//$semester_id = "";
			if ($semesters == null){
				$semester_id == "";
			}else{
				$semester_id = $semester_id == "" ? $semesters[0]->id : $semester_id;
			}	 
			//query view data
			$mcon= Yii::app()->db;
				
			$sqlall='SELECT S.id as student_id,S.sid as sid, S.name as student_name, VKTA.id as kelas_tahun_ajaran_id,kelas_id,VKTA.tahun_ajaran_id,tingkatan_id,tingkatan_name,VKTA.department_id,wali_kelas_id,kelas_name, JP.pelajaran_id
,case when NP.id is null then "0" else NP.nilai end  as nilai
,NP.task_detail_id as task_detail_id,NP.name as task_detail_name, NP.notes, NP.task_date, NP.task_name as task_name, NP.task_group,NP.semester_id
FROM  v_kelas_tahun_ajaran VKTA
LEFT JOIN m_student_class SC on SC.kelas_tahun_ajaran_id=VKTA.id
LEFT JOIN m_student S on SC.student_id=S.id
LEFT JOIN
(SELECT JP.kelas_tahun_ajaran_id, JP.pelajaran_id
FROM `m_jadwal_pelajaran` JP) JP on JP.kelas_tahun_ajaran_id=VKTA.id
left outer join
(SELECT P.id, P.nilai,P.student_id,TPD.kelas_tahun_ajaran_id,TP.pelajaran_id,TPD.name, TPD.notes, TPD.task_date, TP.name as task_name, TP.task_group,(SELECT id FROM m_semester WHERE TPD.task_date BETWEEN start_date AND end_date LIMIT 1 ) as semester_id,TPD.id as task_detail_id
FROM `m_penilaian` P
left join m_task_pelajaran_detail TPD on P.task_pelajaran_detail_id=TPD.id
left join m_task_pelajaran TP on TPD.task_pelajaran_id=TP.id
where TP.is_active=true ) NP on (NP.student_id=S.id and NP.pelajaran_id=JP.pelajaran_id and NP.kelas_tahun_ajaran_id=VKTA.id )
';
			$where_taskgroup=' where task_group IS NOT NULL  and semester_id=:semester_id and VKTA.tahun_ajaran_id=:tahun_ajaran_id and VKTA.id=:kelas_tahun_ajaran_id  and VKTA.department_id=:department_id';
			 
			$where_student=' where S.id IS NOT NULL  and VKTA.tahun_ajaran_id=:tahun_ajaran_id and VKTA.id=:kelas_tahun_ajaran_id  and VKTA.department_id=:department_id and S.id='.$student_id;	
		 
			$sqlall_task=$sqlall.$where_taskgroup;
			$sqlall_student=$sqlall.$where_student;
		
 
		
			$sqltaskname = $sqlall_student . " group by student_id";
			$m_students=$mcon->createCommand($sqltaskname);
			$m_students->bindValue('tahun_ajaran_id',$tahun_ajaran_id);
			$m_students->bindValue('kelas_tahun_ajaran_id',$kelas_tahun_ajaran_id);
			$m_students->bindValue('department_id',$department_id);
			$m_students=$m_students->queryAll();
			$viewdata="";
			foreach ($m_students as $m_student){
				$student_id	= $m_student["student_id"];
				$viewdata .= "STUDENT'S NAME : " . $m_student["student_name"] . "<BR>STUDENT'S NUMBER : " .$m_student["sid"]."<BR>CLASS : ".	$m_student["tingkatan_name"];	$m_student["kelas_name"].'<br><br>';
				$sqltaskname = $sqlall_task . " group by task_group";
				$mPenilaianCommands=$mcon->createCommand($sqltaskname);
				$mPenilaianCommands->bindValue('tahun_ajaran_id',$tahun_ajaran_id);
				$mPenilaianCommands->bindValue('kelas_tahun_ajaran_id',$kelas_tahun_ajaran_id);
				$mPenilaianCommands->bindValue('semester_id',$semester_id);
				$mPenilaianCommands->bindValue('department_id',$department_id);
				$mPenilaianCommands=$mPenilaianCommands->queryAll();
				$viewdata .="<table class=nilai><tr><td rowspan=2>Pelajaran</td>";
				foreach ($mPenilaianCommands as $mPenilaianCommand){
					$sqltask_count = $sqlall_task . " and task_group =:task_group group by task_group,semester_id, task_detail_name";
					$mPenilaianCommandsCountings=$mcon->createCommand($sqltask_count);
					$mPenilaianCommandsCountings->bindValue('task_group',$mPenilaianCommand["task_group"]);
					$mPenilaianCommandsCountings->bindValue('tahun_ajaran_id',$tahun_ajaran_id);
					$mPenilaianCommandsCountings->bindValue('kelas_tahun_ajaran_id',$kelas_tahun_ajaran_id);
					$mPenilaianCommandsCountings->bindValue('semester_id',$semester_id);
					$mPenilaianCommandsCountings->bindValue('department_id',$department_id);
					$mPenilaianCommandsCountings=$mPenilaianCommandsCountings->queryAll();
					$rowCount = count($mPenilaianCommandsCountings);
					$viewdata .= '<td colspan='.$rowCount.'>'.$mPenilaianCommand["task_name"].'</td>';
				}
				$viewdata .= '</tr>';
				$setcount=0;
				$setarray = array();
				foreach ($mPenilaianCommands as $mPenilaianCommand){
					$sqltask_count = $sqlall_task . " and task_group =:task_group  group by task_group,semester_id, task_detail_name order by task_detail_id";
					$mPenilaianCommandsCountings=$mcon->createCommand($sqltask_count);
					$mPenilaianCommandsCountings->bindValue('task_group',$mPenilaianCommand["task_group"]);
					$mPenilaianCommandsCountings->bindValue('tahun_ajaran_id',$tahun_ajaran_id);
					$mPenilaianCommandsCountings->bindValue('kelas_tahun_ajaran_id',$kelas_tahun_ajaran_id);
					$mPenilaianCommandsCountings->bindValue('semester_id',$semester_id);
					$mPenilaianCommandsCountings->bindValue('department_id',$department_id);
					$mPenilaianCommandsCountings=$mPenilaianCommandsCountings->queryAll();
					$rowCount = count($mPenilaianCommandsCountings);
					//				$setarray[] = $mPenilaianCommand['task_group'];
					for ($xid=1; $xid<$rowCount+1; $xid++)
					{
					$viewdata .= '<td  >'.$xid.'</td>';
					}
					foreach ($mPenilaianCommandsCountings as $mPenilaianCommandsCounting){
					$setarray[$mPenilaianCommand['task_group']][] = $mPenilaianCommandsCounting['task_detail_id'];
				 }
				$setcount= $setcount+1;
				}
				$viewdata .= '</tr>';
					$sqlpel='SELECT VKTA.id as kelas_tahun_ajaran_id, karyawan_id, pelajaran_id,name, tahun_ajaran_id, kelas_id, wali_kelas_id, tingkatan_id, VKTA.department_id FROM `m_jadwal_pelajaran` JP
				left join v_kelas_tahun_ajaran VKTA
				on JP.kelas_tahun_ajaran_id=VKTA.id
				left join m_pelajaran P
				on P.id = pelajaran_id where   VKTA.tahun_ajaran_id=:tahun_ajaran_id and VKTA.id=:kelas_tahun_ajaran_id and VKTA.department_id=:department_id ';
				$sqlpel = $sqlpel . "   group by pelajaran_id";
						$mpelajarans=$mcon->createCommand($sqlpel);
						$mpelajarans->bindValue('tahun_ajaran_id',$tahun_ajaran_id);
				$mpelajarans->bindValue('kelas_tahun_ajaran_id',$kelas_tahun_ajaran_id);
				$mpelajarans->bindValue('department_id',$department_id);
						$mpelajarans=$mpelajarans->queryAll();
		
					$inCounter=0;$xid=0;
					foreach ($mpelajarans as $mpelajaran){
					$pelajaran_id=$mpelajaran["pelajaran_id"];
					$viewdata .=  '<tr><td>'.$mpelajaran["name"].'</td>';
					foreach ($mPenilaianCommands as $mPenilaianCommand){
					$rowCount=count($setarray[$mPenilaianCommand["task_group"]]);
					for ($xid=0; $xid<$rowCount; $xid++)
					{
							$sqlpenilaian = $sqlall_task . "  and JP.pelajaran_id =:pelajaran_id and S.id =:student_id  and NP.task_detail_id=:task_detail_id  order by task_detail_id";
					$mpelajarans_nilai=$mcon->createCommand($sqlpenilaian);
							$setnilai=$setarray[$mPenilaianCommand["task_group"]][$xid];
							$mpelajarans_nilai->bindValue('task_detail_id',$setnilai);
					$mpelajarans_nilai->bindValue('student_id',$student_id);
					$mpelajarans_nilai->bindValue('pelajaran_id',$pelajaran_id);
					$mpelajarans_nilai->bindValue('tahun_ajaran_id',$tahun_ajaran_id);
					$mpelajarans_nilai->bindValue('kelas_tahun_ajaran_id',$kelas_tahun_ajaran_id);
					$mpelajarans_nilai->bindValue('semester_id',$semester_id);
					$mpelajarans_nilai->bindValue('department_id',$department_id);
					$mpelajarans_nilai=$mpelajarans_nilai->queryAll();
					$rowCountsub = count($mpelajarans_nilai);
					if ($rowCountsub==0){$viewdata .=  ' <td>&nbsp;</td>';}
					else {
					foreach ($mpelajarans_nilai as $mpelajaran_nilai){
					$viewdata .=  ' <td>'.$mpelajaran_nilai["nilai"].'</td>';
					}}
					}
		
					}
					$viewdata .= '</tr>';
					}
							$viewdata .="</table><br>";
			}
						
		$vw="##".$sqlall_student."$$".$kelas_tahun_ajaran_id.'-'.$tahun_ajaran_id;
						
					 
					$data= array(
//					"department_id"=>$department_id,
/*					"karyawan_name"=>$karyawan_name,
					"karyawan_id"=>$karyawan_id,
*///					"tahun_ajaran_id"=>$tahun_ajaran_id,
//					"tingkatans"=>$tingkatans,		
//					"tingkatan_id"=>$tingkatan_id,	
//					"tahunAjarans"=>$tahunAjarans,
					"semesters"=>$semesters,
					"semester_id" => $semester_id,
//					"kelass"=>$kelass,
//					"kelas_tahun_ajaran_id"=>$kelas_tahun_ajaran_id,
/*					"department_name"=>$department_name,
					"students"=>$students,
					"walikelas_id"=>$walikelas_id,			
*///					"student_id"=>$student_id,			
					"viewdata" =>$viewdata,
					"vw" =>$vw

		
			);
					$this->render('rptByStudent',$data);
		}
		
		
		protected function beforeRender($view){
			if ($this->subMenu != ""){
				$this->title = $this->mainMenu . " - " .$this->subMenu;
			}
			return true;
		}
		
	public function actionPopupWindow()
	{
		$this->layout = "//layouts/ly_blank";
		$this->render('popupWindow');
	}		
		
	}
?>